/*
   PROCEDURES LIST :	
	REMOVE_DOMAIN (p_Nom TEXT) RETURNS BOOLEAN 
	REMOVE_CONCEPT (p_Nom TEXT) RETURNS BOOLEAN
	REMOVE_TS (p_Nom TEXT,Concept, boolean) RETURNS BOOLEAN
	REMOVE_TG (p_Nom TEXT,Concept, boolean) RETURNS BOOLEAN 
	REMOVE_ASSOCIATION (p_Nom TEXT) RETURNS BOOLEAN
	REMOVE_TERMVDT (p_Nom TEXT) RETURNS BOOLEAN
	REMOVE_TERMNVDT (p_Nom TEXT) RETURNS BOOLEAN
*/


/*************************** Classic Remove FUNCTIONS *************************************/
    
/*
*	Function : REMOVE_DOMAIN 
*	Description : Supprime un domain
*/
CREATE OR REPLACE FUNCTION REMOVE_DOMAIN (p_Nom TEXT) RETURNS BOOLEAN AS $$
DECLARE
	domain_oid OID;
BEGIN
/* 0- verification des entrées */
	domain_oid = (SELECT OID FROM Domains WHERE Nom = p_Nom);
	
	IF (domain_oid IS NOT NULL) THEN 
		DELETE FROM Domains  WHERE OID = domain_oid;
		DELETE FROM Domains  WHERE OID = domain_oid;
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_CONCEPT 
*	Description : Supprime un concept
*/
CREATE OR REPLACE FUNCTION REMOVE_CONCEPT (p_Nom TEXT) RETURNS BOOLEAN AS $$
DECLARE
	concept_oid OID;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Nom);
	
	IF (concept_oid IS NOT NULL) THEN 
		DELETE FROM Concepts  WHERE OID = concept_oid;
		DELETE FROM Concepts  WHERE OID = concept_oid;
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_TERMVDT
*	Description : Supprime un Term Vedette
*/
CREATE OR REPLACE FUNCTION REMOVE_TERMVDT(p_Nom TEXT) RETURNS BOOLEAN AS $$
DECLARE
	termvdt_oid OID;
BEGIN
/* 0- verification des entrées */
	termvdt_oid = (SELECT OID FROM TermVdts WHERE Nom = p_Nom);
	
	IF (termvdt_oid IS NOT NULL) THEN 
		DELETE FROM TermVdts  WHERE OID = termvdt_oid;
		DELETE FROM TermVdts  WHERE OID = termvdt_oid;
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_TERMNVDT
*	Description : Supprime un Term Non Vedette
*/
CREATE OR REPLACE FUNCTION REMOVE_TERMNVDT(p_Nom TEXT) RETURNS BOOLEAN AS $$
DECLARE
	termnvdts_oid OID;
BEGIN
/* 0- verification des entrées */
	termnvdts_oid = (SELECT OID FROM TermNonVdts WHERE Nom = p_Nom);
	
	IF (termnvdts_oid IS NOT NULL) THEN 
		DELETE FROM TermNonVdts  WHERE OID = termnvdts_oid;
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : REMOVE_TS 
*	Description : Supprime le p_TS de la liste des TS de p_concept 
*/
CREATE OR REPLACE FUNCTION REMOVE_TS (p_Concept TEXT, p_TS TEXT, p_fxCall BOOLEAN) RETURNS BOOLEAN AS $$
DECLARE
	concept_oid OID;
	TS_oid OID;
	t OID;
	TSS OID[];
	TSS_NEW OID[];
	tmp boolean;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	TS_oid = (SELECT OID FROM Concepts WHERE Nom = p_TS);
	
	IF (concept_oid IS NULL AND TS_oid IS NULL) THEN
		RETURN FALSE;
	END IF;
	
/* Récupérer les TS du concept */
	SELECT INTO TSS TS FROM Concepts WHERE OID = concept_oid; 
	 
/* Si le TS n'existe pas */
	IF (TSS IS NULL) THEN
		RETURN FALSE;
	END IF;
		
	IF (TSS @> ARRAY[TS_oid] = FALSE) THEN
		RETURN FALSE;
	END IF;
		
/* Supprimer TS_oid depuis TSS */
	FOREACH t IN  ARRAY TSS LOOP
		IF (t != TS_oid) THEN
			TSS_NEW = TSS_NEW || t;
		END IF;
	END LOOP;
	
/* Mettre à jours TS du concept */
	UPDATE Concepts SET TS = TSS_NEW WHERE OID = concept_oid;

/* Suppression le concept dans les TG du TS si ce n'est pas un appel depuis la fonction REMOVE_TG */
	IF (p_fxCall = FALSE) THEN
		SELECT * FROM REMOVE_TG (p_TS, p_Concept, TRUE) INTO tmp;	
	END IF;
	
	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_TG
*	Description : Supprime le p_TG de la liste des TG de p_concept 
*/
CREATE OR REPLACE FUNCTION REMOVE_TG (p_Concept TEXT, p_TG TEXT, p_fxCall BOOLEAN) RETURNS BOOLEAN AS $$
DECLARE
	concept_oid OID;
	TG_oid OID;
	t OID;
	TGG OID[];
	TGG_NEW OID[];
	tmp boolean;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	TG_oid = (SELECT OID FROM Concepts WHERE Nom = p_TG);
	
	IF (concept_oid IS NULL AND TG_oid IS NULL) THEN
		RETURN FALSE;
	END IF;
	
/* Récupérer les TG du concept */
	SELECT INTO TGG TG FROM Concepts WHERE OID = concept_oid;  
	
/* Si le TG n'existe pas */
	IF (TGG IS NULL) THEN
		RETURN FALSE;
	END IF;
		
	IF (TGG @> ARRAY[TG_oid] = FALSE) THEN
		RETURN FALSE;
	END IF;

/* Supprimer TG_oid depuis TG */
	FOREACH t IN  ARRAY TGG LOOP
		IF (t != TG_oid) THEN
			TGG_NEW = TGG_NEW || t;
		END IF;
	END LOOP;
	
/* Mettre à jours TG du concept */
	UPDATE Concepts SET TG = TGG_NEW WHERE OID = concept_oid;

/* Suppression le concept dans les TS du TG si ce n'est pas un appel depuis la fonction REMOVE_TS */
	IF (p_fxCall = FALSE) THEN
		SELECT * FROM REMOVE_TS (p_TG, p_Concept, TRUE) INTO tmp;
	END IF;			
	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : REMOVE_ASSOCIATION
*	Description : Supprime une association
*/
CREATE OR REPLACE FUNCTION REMOVE_ASSOCIATION (p_Nom TEXT, p_Concept1 TEXT, p_Concept2 TEXT, p_Reflex BOOLEAN) RETURNS BOOLEAN AS $$
DECLARE
	association_oid OID;
BEGIN
/* 0- verification des entrées */
	association_oid = EXIST_ASSOCIATION (p_Nom, p_Concept1, p_Concept2, p_Reflex);
	
	IF (association_oid > 0) THEN
		DELETE FROM Associations  WHERE Nom = p_Nom AND 
			concept1 = (SELECT OID FROM Concepts WHERE Nom = p_Concept1) AND 
			concept2 = (SELECT OID FROM Concepts WHERE Nom = p_Concept2) AND
			Reflex = p_Reflex;
		IF p_Reflex = TRUE THEN
			DELETE FROM Associations  WHERE Nom = p_Nom AND 
			concept1 = (SELECT OID FROM Concepts WHERE Nom = p_Concept2) AND 
			concept2 = (SELECT OID FROM Concepts WHERE Nom = p_Concept1) AND
			Reflex = p_Reflex;
		END IF;
		
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;


/*************************** TRIGGER FUNCTIONS *************************************/

/*========================= Domains TRIGGER =========================*/	
/*
*	Function : REMOVE_FROM_THESO_T
*	Description : Supprime tous les concepts d'un domaine, appelée ON DELETE DOMAIN
*/
CREATE OR REPLACE FUNCTION REMOVE_FROM_THESO_T() RETURNS TRIGGER AS $$
DECLARE
  t OID;
  domains OID[];
  domains_new OID[];	
BEGIN

/* Récupération des domaines du thesaurus du domain à supprimer */
SELECT Thesaurus.Domains FROM Thesaurus WHERE OID = OLD.Theso INTO domains;
 
/* Supprimer domain_oid depuis Domains de theso */
	IF (domains IS NOT NULL) THEN
		FOREACH t IN  ARRAY domains LOOP
			IF (t != OLD.OID) THEN
				domains_new = domains_new || t;
			END IF;
		END LOOP;
	END IF;
	
/* Mettre à jours Domains du theso */
	UPDATE Thesaurus SET Domains = domains_new WHERE OID = OLD.Theso;
				
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_CONCEPT_T
*	Description : Supprime tous les concepts d'un domaine, appelée ON DELETE DOMAIN
*/
CREATE OR REPLACE FUNCTION REMOVE_CONCEPT_T() RETURNS TRIGGER AS $$
DECLARE
  t OID;	
BEGIN
	IF (OLD.Concepts IS NOT NULL) THEN
		FOREACH t IN  ARRAY OLD.Concepts LOOP
			DELETE FROM Concepts WHERE OID = t;
			DELETE FROM Concepts WHERE OID = t;
		END LOOP;
	END IF;
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;



/*========================= Concepts TRIGGER =========================*/	
	
/*
*	Function : REMOVE_FROM_DOMAIN_T
*	Description : Supprime le concepts de Concepts de son domaine, appelée ON DELETE CONCEPT
*/
CREATE OR REPLACE FUNCTION REMOVE_FROM_DOMAIN_T() RETURNS TRIGGER AS $$
DECLARE
  t OID;
  concepts2 OID[];
  concepts_new OID[];	
BEGIN

/* Récupération des concepts du domain du concept à supprimer */
SELECT Domains.Concepts FROM Domains WHERE OID = OLD.Domain INTO concepts2;
 
/* Supprimer domain_oid depuis Domains de theso */
	IF (concepts2 IS NOT NULL) THEN
		FOREACH t IN  ARRAY concepts2 LOOP
			IF (t != OLD.OID) THEN
				concepts_new = concepts_new || t;
			END IF;
		END LOOP;
	END IF;
/* Mettre à jours Domains du theso */
	UPDATE Domains SET Concepts = concepts_new WHERE OID = OLD.Domain;
				
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_TS_T
*	Description : Supprime le concept depuis les TG des concepts associer en tant que TS du concept, appelée ON DELETE CONCEPT
*/
CREATE OR REPLACE FUNCTION REMOVE_TS_T() RETURNS TRIGGER AS $$
DECLARE	
	t OID;
	tmp boolean;
BEGIN
/* On supprime tout les Relations TS, REMOVE_TS se chargera de supprimer le concepts dans TG des concepts reliés */
	IF (OLD.TS IS NOT NULL) THEN	
		FOREACH t IN  ARRAY OLD.TS LOOP
			SELECT * FROM  REMOVE_TS (OLD.nom, (SELECT nom FROM Concepts WHERE OID = t), FALSE) INTO tmp;
		END LOOP;
	END IF;
	
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_TG_T
*	Description : Supprime le concept depuis les TS des concepts associer en tant que TG du concept, appelée ON DELETE CONCEPT
*/
CREATE OR REPLACE FUNCTION REMOVE_TG_T () RETURNS TRIGGER AS $$
DECLARE	
	t OID;
	tmp boolean;
BEGIN
/* On supprime tout les Relations TG, REMOVE_TG se chargera de supprimer le concepts dans TS des concepts reliés */
	IF (OLD.TG IS NOT NULL) THEN	
		FOREACH t IN  ARRAY OLD.TG LOOP
			SELECT * FROM  REMOVE_TG (OLD.nom, (SELECT nom FROM Concepts WHERE OID = t), FALSE) INTO tmp;
		END LOOP;
	END IF;
	
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_ASSOCIATION_T
*	Description : Supprime les associations relier à un concept, appelée ON DELETE CONCEPT
*/
CREATE OR REPLACE FUNCTION REMOVE_ASSOCIATION_T () RETURNS TRIGGER AS $$
BEGIN
	DELETE FROM Associations WHERE concept1 = OLD.OID OR concept2 = OLD.OID;
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_TERMVDT_T
*	Description : Supprime le Term vedette d'un concept, appelée ON DELETE CONCEPT
*/
CREATE OR REPLACE FUNCTION REMOVE_TERMVDT_T() RETURNS TRIGGER AS $$
BEGIN
	DELETE FROM TermVdts WHERE OID = OLD.TermVedette;
	DELETE FROM TermVdts WHERE OID = OLD.TermVedette;
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;

/*========================= TERMVDT TRIGGER =========================*/

/*
*	Function : REMOVE_FROM_CONCEPT_T
*	Description : Supprime le Terms vedette dans le concept associé, appelée ON DELETE TERMVEDETTE
*/
CREATE OR REPLACE FUNCTION REMOVE_FROM_CONCEPT_T() RETURNS TRIGGER AS $$
BEGIN
	UPDATE Concepts SET TermVedette = 0 WHERE OID = OLD.Concept;
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : REMOVE_TERMNVDT_T
*	Description : Supprime les Terms Non vedettes d'un Term Vedette, appelée ON DELETE TERMVEDETTE
*/
CREATE OR REPLACE FUNCTION REMOVE_TERMNVDT_T() RETURNS TRIGGER AS $$
DECLARE
  t OID;	
BEGIN
	IF (OLD.EP IS NOT NULL) THEN
		FOREACH t IN  ARRAY OLD.EP LOOP
			DELETE FROM TermNonVdts WHERE OID = t;
		END LOOP;
	END IF;			
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;

/*========================= TERMNVDT TRIGGER =========================*/

/*
*	Function : REMOVE_FROM_TERMVDT_T
*	Description : Supprime les Terms Non vedettes d'un Term Vedette, appelée ON DELETE TERMNVEDETTE
*/
CREATE OR REPLACE FUNCTION REMOVE_FROM_TERMVDT_T() RETURNS TRIGGER AS $$
DECLARE
  t OID;
  termvdts2 OID[];
  termvdts_new OID[];	
BEGIN

termvdts_new = ARRAY[]::OID[];

/* Récupération des EP du Term vedette du Term Non vedette à supprimer */
SELECT EP FROM TermVdts WHERE OID = OLD.EM INTO termvdts2;
 
/* Supprimer domain_oid depuis Domains de theso */
	IF (termvdts2 IS NOT NULL) THEN
		FOREACH t IN  ARRAY termvdts2 LOOP
			IF (t != OLD.OID) THEN
				termvdts_new = termvdts_new || t;
			END IF;
		END LOOP;
	END IF;
	
/* Mettre à jours EP du Term Vedette */
	UPDATE TermVdts SET EP = termvdts_new WHERE OID = OLD.EM;
				
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;
